using System;
using System.Collections.Generic;
using System.Text;
using DAL;
using DTO;
using System.Data;
using System.Data.OleDb;	

namespace BLL
{
    public class SinhvienDAL
    {
        public List<SINHVIEN> GetAll()
        {
            SINHVIEN sinhvien;
            List<SINHVIEN> list = new List<SINHVIEN>();
            DataProvider dataprovider = new DataProvider();
            DataTable datatable = new DataTable();
            datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN", null);
            for (int i = 0; i < datatable.Rows.Count; i++)
            {
                sinhvien = new SINHVIEN();
                sinhvien.MaSV = datatable.Rows[i]["MaSV"].ToString();
                sinhvien.Ten = datatable.Rows[i]["Ten"].ToString();
                sinhvien.Holot = datatable.Rows[i]["Holot"].ToString();
                list.Add(sinhvien);
            }
            return list;
        }
        public SINHVIEN GetSINHVIEN(string masinhvien)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@masinhvien", OleDbType.Char);
            parameters[0].Value = masinhvien;
            DataTable datatable = new DataTable();
            datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN where MaSV = @masinhvien", parameters);
            SINHVIEN sinhvien = new SINHVIEN();
            if (datatable.Rows.Count != 0)
            {
                sinhvien.MaSV = datatable.Rows[0]["MaSV"].ToString();
                sinhvien.Ten = datatable.Rows[0]["Ten"].ToString();
                sinhvien.Holot = datatable.Rows[0]["Holot"].ToString();
                return sinhvien;
            }
            else
                return null;
        }
        public bool UpdateSINHVIEN(string masinhvien,string holot, string ten, string masinhviencu)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[4];
            parameters[0] = new OleDbParameter("@masinhvien", OleDbType.Char);
            parameters[0].Value = masinhvien;
            parameters[1] = new OleDbParameter("@holot", OleDbType.Char);
            parameters[1].Value = holot;
            parameters[2] = new OleDbParameter("@ten", OleDbType.Char);
            parameters[2].Value = ten;
            parameters[3] = new OleDbParameter("@masinhviencu", OleDbType.Char);
            parameters[3].Value = masinhviencu;
            return dataprovider.ExecuteNonSelectQuery("update SINHVIEN set MaSV=@masinhvien,Holot=@holot, Ten=@ten where MaSV = @masinhviencu", parameters);
        }
        public bool InsertSINHVIEN(string masinhvien, string holot, string ten)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@masinhvien", OleDbType.Char);
            parameters[0].Value = masinhvien;
            parameters[1] = new OleDbParameter("@ten", OleDbType.Char);
            parameters[1].Value = ten;
            parameters[2] = new OleDbParameter("@holot", OleDbType.Char);
            parameters[2].Value = holot;
            return dataprovider.ExecuteNonSelectQuery("insert into SINHVIEN(MaSV,Ten,Holot) values(@masinhvien,@ten,@holot)", parameters);
        }

    }
}
